﻿Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlServerCe
Imports System.Drawing
Imports System.Collections
Imports System.Windows.Forms
Imports System.Data.Common

Module DatabaseFunction
    Private conn As New SqlCeConnection()
    Private cmd As SqlCeCommand
    Private sqlCeDataAdapter As SqlCeDataAdapter
    Private sqlCeTransaction As SqlCeTransaction

    'select statement
    Public Function LoadData(ByVal sqlStr As String, ByVal paramList As Hashtable) As DataTable
        Dim tmpDS As New DataTable
        Try
            conn = New SqlCeConnection(sCon)

            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If

            cmd = conn.CreateCommand
            cmd.CommandText = sqlStr

            For Each de As DictionaryEntry In paramList
                cmd.Parameters.AddWithValue(de.Key, de.Value)
            Next de
            'cmd.ExecuteNonQuery()

            sqlCeDataAdapter = New SqlCeDataAdapter(cmd)
            sqlCeDataAdapter.Fill(tmpDS)
            Return tmpDS

        Catch ex As Exception
            tmpDS.RejectChanges()
            MsgBox("LOCAL CONNECTION : " & sqlStr & vbCrLf & ex.Message, MsgBoxStyle.Information, sysname & " - LoadData")
        Finally
            If conn.State = ConnectionState.Open Then conn.Close()
        End Try

        Return tmpDS
    End Function

    'update, insert, delete
    Public Function ExecuteSQL(ByVal sqlStr As String, ByVal paramList As Hashtable) As Boolean
        Dim success As Boolean = True
        Try
            conn = New SqlCeConnection(sCon)

            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If

            sqlCeTransaction = conn.BeginTransaction
            cmd = conn.CreateCommand
            cmd.CommandText = sqlStr

            For Each de As DictionaryEntry In paramList
                cmd.Parameters.AddWithValue(de.Key, de.Value)
            Next de

            'cmd.CommandTimeout = 1800
            cmd.Transaction = sqlCeTransaction
            cmd.ExecuteNonQuery()
            sqlCeTransaction.Commit()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Information, sysname & " - ex")
            MsgBox("LOCAL CONNECTION : " & sqlStr & vbCrLf & ex.Message, MsgBoxStyle.Information, sysname & " - ExecuteSQL")
            sqlCeTransaction.Rollback()

            success = False
        Finally
            If conn.State = ConnectionState.Open Then conn.Close()
        End Try

        Return success
    End Function

    'select statement
    Public Function LoadData_BAK(ByVal sqlStr As String) As DataTable
        Dim tmpDS As New DataTable
        Try
            conn = New SqlCeConnection(sCon)

            If conn.State = ConnectionState.Closed Then
                conn.Open()
            End If

            'cmd = conn.CreateCommand
            'cmd.CommandText = sqlStr
            'cmd.ExecuteNonQuery()

            sqlCeDataAdapter = New SqlCeDataAdapter(sqlStr, conn)
            sqlCeDataAdapter.Fill(tmpDS)
            Return tmpDS

        Catch ex As Exception
            tmpDS.RejectChanges()
            MsgBox("LOCAL CONNECTION : " & sqlStr & vbCrLf & ex.Message, MsgBoxStyle.Information, sysname & " - LoadData")
        Finally
            If conn.State = ConnectionState.Open Then conn.Close()
        End Try

        Return tmpDS
    End Function

End Module
